SQL: Create database & tables

November 24, 2020

Database

Create a new database

1. With phpMyAdmin interface

Collation: use utf8_general_ci to avoid any compatibility issues. ci means "case-sensitive": upper case and lower case are considered the same.

2. With command line

Syntax convention: SQL queries are written in upper case and values are in low case. This habit allows to differentiate functions (not changing) and variables (changing).

Code

CREATE DATABASE database_name ;
DEFAULT CHARACTER SET utf8 ;
DEFAULT COLLATE utf8_general_ci

To get a list of variables for the current database:

Code

USE database_name ;
SHOW VARIABLES

Delete a database

1. With phpMyAdmin interface

Click on the database name on le left handside menu
"Operations" tab
Section "Remove database": click on "Drop database (DROP)"

Warning: In MySQL, actions are not reversible. Each action cannot be cancelled once done. This is valid for database and tables deletion (be carefull before delete then).

2. With command line

Code

DROP DATABASE database_name

Table

Create a table

1. With phpMyAdmin interface

Columns:

Name: space is not allowed. So use "-" or "_" characters instead.
Type:
- List of options and a detailed explanation for each: click here.
- While overing with mouse on each selector's option, a tooltip displays with explanations on it.
Length: Enter a int value only to allow a maximum lenght (only for text and int values: keep it blank for DATE types). If not usefull: leave blank.
Default: Enter a default value only if the filed is not mandatory.
Collation: If left blank, then the collation defined for database will be applied. In general, leave blank to avoid conflicts and to much complexity.
Attributes:
- BINARY:
- UNSIGNED: No negative integers (no minus sign), but only positive ones. This allows more memory allows for the field for positive values (for example: for TINYINT, the value for UNSIGNED can be from 0 to 255, instead of: from -127 to 128 by default.
- UNSIGNED ZEROFILL: Same as UNSIGNED but with zeros added until the field length is riched (example: if length filled to 3, the value will be set to "001" for a "1" input.
- on update CURRENT_TIMESTAMP: usefull for post update fields for example (to show the modification date and time). If using this, then the TYPE must be set to TIMESTAMP.
Null: Check if null values are allowed for this field (very often we leave this unchecked)
Index / A_I: see paragraph below

Storage Engine: We only use 2 options, depending on our needs:

InnoDB: most often used. A bit slower than MyISAM but allows to recover data if needed.
MyISAM: faster and allows to do FULLTEXT searches. Downside: may be problematic with some characters
Complete list of pros and cons for each here

Result

SQL : image 1 for lesson "Create database & tables"

SQL : image 2 for lesson "Create database & tables"

2. SQL Code generation

This is used to generate SQL tables code to insert inside PHP script. So the easiest practice is:

Create a sample table, filling all fields as detailed above (with visual interface)
Paste the code below inside the command line console of phpMyAdmin
On next page, click on "+ options" and check "full text" (see image below)
Copy the generated SQL code and paste it inside the PHP script.

Code

USE database_name ;
SHOW CREATE TABLE table_name

Result

SQL : image 3 for lesson "Create database & tables"

Delete a table

Code

DROP TABLE database_name

Modify a table

Add a table column:

Code

ALTER TABLE table_name ;
ADD column_name TYPE (length) ATTRIBUTES

Example: 
ALTER TABLE users ;
ADD username VARCHAR (255) NOT NULL

Delete a column:

Code

ALTER TABLE table_name DROP column_name

Change column's name:

Code

ALTER TABLE table_name CHANGE column_name_current column_name_new TYPE NOT NULL

Example:
ALTER TABLE users CHANGE date signup_date DATETIME NOT NULL

Change column's type:

Code

ALTER TABLE table_name MODIFY column_name TYPE_NEW ATTRIBUTES

Example:
ALTER TABLE users MODIFY signup_date TIMESTAMP NOT NULL

Keys & Indexes

Primary key

For each new table created, it's necessary to define a primary key:

This is the reference id for each table's entry
Very often, we create a new column called "id" and define it as PRIMARY ("index" column)
This column allows to target each element as unique (like a post id, a user id, etc...)
A_I (auto_increment): allows to add +1 to the id for each new table entry. For primary columns, we always check this.

Index

Other Index types:

UNIQUE: forbid to have 2 identical values. Example, we can use it for page slugs (having 2 page with the same slug is not possible). While the primary key is in general dedicated to ids, the INDEX option is for other values that need to be unique.
INDEX: Allows quicker queries (improve performance of database). Downturn: will take more space in the database. So, we'll avoid using this option for columns containign hundreds of values. We'll keep it only for few values (example: gender (2 values), categories (ten for example), etc.)
FULLTEXT: Only available for MyISAM storage engine. This will allow to optimize reasearches.

▲